/**
 * Copyright (c) 2002 qc&Bull Co.,LTD
 * All right reserved.
 */
package cn.zxd.util;

import org.jdom.Document;
import org.jdom.output.XMLOutputter;

import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;

/**
 *
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class CDB2Blob
{
    public CDB2Blob()
    {
    }

    public static void main(String[] args) throws Exception
    {
//        CDB2Blob CDB2Blob1 = new CDB2Blob();
//        Connection conn = DBConnPool.getConnection();
//        //        String ls = "INSERT INTO LCPolPrint(MainPolNo, PrtTimes, Operator,
//        // MakeDate, MakeTime, ModifyDate, ModifyTime,PolInfo, PolType) VALUES "
//        //                    + "('240110000000035', 1, 'ac', '2004-12-28', '18:23:23',
//        // '2004-12-28', '18:24:24',? ,'2')";
//        String ls = "insert into test_blob(id,blobcol) values ('1',?)";
//        String str = "e:/240110000000035.xml";
//        FileInputStream fi = new FileInputStream(str);
//        CDB2Blob1.InsertBlankBlobRecord(fi, ls, conn);
    }

    /**
     * 修改某个表的Blob字段
     *
     * @param pInXmlDoc
     * @param pTabName
     * @param pUpdateField
     * @param pWhereSQL
     * @param pConn
     */
    public boolean UpdateBlob(Document pInXmlDoc, String pTabName,
                              String pUpdateField, String pGrpPolNo,
                              Connection pConn)
    {
        // TODO: implement
        PreparedStatement preparedStatement = null;
        //       pConn.prepareStatement("SELECT BOOKCOVER FROM BOOKCOVERS WHERE
        // BOOKISBN=?");
        //       preparedStatement.setString(1, "0738425826");
        //       ResultSet resultSet = preparedStatement.executeQuery();

        Statement stmt = null;
        ResultSet rs = null;
        if (pConn == null)
        { //如果连接没有寸入，则返回false
            System.out.println("CDB2Blob没有传入连接！");
            return false;
        }
        try
        {
            stmt = pConn.createStatement();

            PreparedStatement ps = null;
            InputStream is = null;
            byte[] bytePolInfo = null;

            String szSQL = "SELECT " + pUpdateField + " FROM " + pTabName
                    + " WHERE MainPolNo = '" + pGrpPolNo +
                    "' FOR UPDATE";
            System.out.println("UpdateBlob :" + szSQL);
            preparedStatement = pConn.prepareStatement(szSQL);
            //         rs = preparedStatement.executeQuery();
            rs = preparedStatement.executeQuery();
            //         if (!rs.next()) {
            //           System.out.println("CDB2Blob该查询条件没有查询到记录！SQL为：" + szSQL);
            //           rs.close();
            //           stmt.close();
            //           return false;
            //         }
            while (rs.next())
            {
                Blob blob = rs.getBlob(1);
                //             System.out.println(blog.length());
                //             InputStream os = blog.getBinaryStream();
                //         XmlExport xmlexport = new XmlExport(); //新建一个XmlExport的实例
//                XMLOutputter outputter = new XMLOutputter("", false, "UTF-8");
                XMLOutputter outputter = new XMLOutputter();
                //             outputter.output(pInXmlDoc, os);
                //             os.flush();
                //             os.close();
                rs.close();
                stmt.close();
            }

            //         java.sql.Blob blob = rs.getBlob(1);
            //         weblogic.jdbc.vendor.oracle.OracleThinBlob wbb =
            // (weblogic.jdbc.vendor.
            //             oracle.OracleThinBlob) blob;
            //         OutputStream os = wbb.getBinaryOutputStream();
            //         //这里必须用weblogic的OracleThinBlob类得到os，不能用Oracle的BLOB类和java的Blob类!!-Fanym
            //         XmlExport xmlexport = new XmlExport(); //新建一个XmlExport的实例
            //         XMLOutputter outputter = new XMLOutputter("", false, "UTF-8");
            //         outputter.output(pInXmlDoc, os);
            //         os.flush();
            //         os.close();
            //         rs.close();
            //         stmt.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            System.out.print(ex.toString());
            try
            {
                if (rs != null)
                {
                    rs.close();
                }
                if (stmt != null)
                {
                    stmt.close();
                }
            }
            catch (Exception ex1)
            {
            }
            return false;
        }
        return true;
    }

    /**
     * 修改某个表的Blob字段
     *
     * @param pInStream
     * @param pTabName
     * @param pUpdateField
     * @param pWhereSQL
     * @param pConn
     */
    public boolean UpdateBlob(InputStream pInStream, String pTabName,
                              String pUpdateField, String pWhereSQL,
                              Connection pConn)
    {
        // TODO: implement
        Statement stmt = null;
        ResultSet rs = null;
        if (pConn == null) //如果连接没有寸入，则返回false
        {
            System.out.println("COracleBlob没有传入连接！");
            return false;
        }
        try
        {
            // 得到数据输出对象
            stmt = pConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_UPDATABLE);
            String szSQL = "SELECT " + pUpdateField + " FROM " + pTabName +
                    " WHERE 1=1  " + pWhereSQL;
            rs = stmt.executeQuery(szSQL);
            if (!rs.next())
            {
                System.out.println("COracleBlob该查询条件没有查询到记录！SQL为：" + szSQL);
                rs.close();
                stmt.close();
                return false;
            }
            rs.close();

//            String spSQL = "SELECT " + pUpdateField + " FROM " + pTabName +
//                           " WHERE 1=1  " + pWhereSQL + " FOR UPDATE";
            String spSQL = "UPDATE " + pTabName + " Set " + pUpdateField +
                    " =? " + " WHERE 1=1  " + pWhereSQL;
            System.out.println(spSQL);
            PreparedStatement ps = pConn.prepareStatement(spSQL);
            System.out.println(pInStream.available());
            ps.setBinaryStream(1, pInStream, pInStream.available());
            ps.execute();

//            Blob blob = rs.getBlob(pUpdateField);
//            OutputStream os = ((oracle.sql.BLOB) blob).getBinaryOutputStream();
//            InputStream ins = pInStream;
//            int inData = 0;
//            while ((inData = ins.read()) != -1)
//            {
//                os.write(inData);
//            }
//            os.flush();
//            os.close();

            ps.close();
            stmt.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            try
            {
                if (rs != null)
                {
                    rs.close();
                }
                if (stmt != null)
                {
                    stmt.close();
                }
            }
            catch (Exception ex1)
            {}
            return false;
        }
        return true;
    }

    /**
     * @param pInsertSQL
     * @param pConn
     */
    public boolean DeleteBlobRecord(String pDeleteSQL, Connection pConn)
    {
        // TODO: implement
        Statement stmt = null;
        if (pConn == null)
        { //如果连接没有寸入，则返回false
            System.out.println("CDB2Blob没有传入连接！");
            return false;
        }
        try
        {
            // 得到数据输出对象
            //        stmt = (OracleStatement) pConn.createStatement(OracleResultSet.
            //            TYPE_FORWARD_ONLY, OracleResultSet.CONCUR_UPDATABLE);
            //        String szSQL = pDeleteSQL;
            stmt = pConn.createStatement();
            String sDeleteSQL = pDeleteSQL.replaceFirst("DELETE",
                    "SELECT MAINPOLNO");
            ResultSet rs = stmt.executeQuery(sDeleteSQL);
            if (rs.next())
            {
                stmt.executeUpdate(pDeleteSQL);
            }
            stmt.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            try
            {
                if (stmt != null)
                {
                    stmt.close();
                }
            }
            catch (Exception ex1)
            {
            }
            return false;
        }
        return true;
    }

    /**
     * @param pInsertSQL
     * @param pConn
     */
    public boolean InsertBlankBlobRecord(InputStream pis, String pInsertSQL,
                                         Connection pConn)
    {
        // TODO: implement
        Statement stmt = null;
        if (pConn == null)
        { //如果连接没有寸入，则返回false
            System.out.println("CDB2Blob没有传入连接！");
            return false;
        }
        try
        {
            System.out.println("DB2数据库Blob插入操作sql：" + pInsertSQL);
            PreparedStatement ps = pConn.prepareStatement(pInsertSQL);
//            System.out.println(pis.available());
            ps.setBinaryStream(1, pis, pis.available());
            ps.execute();
            ps.close();
//        stmt = pConn.createStatement();
//        stmt.executeUpdate(pInsertSQL);
//        stmt.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            try
            {
                if (stmt != null)
                {
                    stmt.close();
                }
            }
            catch (Exception ex1)
            {
            }
            return false;
        }
        return true;
    }

    /**
     * 读取指定SQL语句中的某个Blob字段到一个OutStream中。
     *
     * @param pOutStream
     * @param pLocationSQL
     * @param pSelectField
     * @param pConn
     *            如果传入的pConn为null，则在函数内部创建连接。
     */
    public boolean SelectBlob(OutputStream pOutStream, String pTabName,
                              String pSelectField, String pWhereSQL,
                              Connection pConn)
    {
        // TODO: implement
        return false;
    }

    /**
     * 读取指定SQL语句中的某个Blob字段到一个OutStream中。
     *
     * @param pOutStream
     * @param pLocationSQL
     * @param pSelectField
     * @param pConn
     *            如果传入的pConn为null，则在函数内部创建连接。
     */
    public Blob SelectBlob(String pTabName, String pSelectField,
                           String pWhereSQL, Connection pConn)
    {
        // TODO: implement
        Statement stmt = null;
        ResultSet rs = null;
        Blob tOutData = null;

        if (pConn == null)
        { //如果连接没有寸入，则返回false
            System.out.println("CDB2Blob没有传入连接！");
            return null;
        }
        try
        {
            stmt = pConn.createStatement();
            String szSQL;
            szSQL = "SELECT " + pSelectField + " FROM " + pTabName
                    + " WHERE 1=1 " + pWhereSQL;
            rs = stmt.executeQuery(szSQL);
            if (!rs.next())
            {
                System.out.println("找不到打印数据,SQL为：" + szSQL);
                rs.close();
                stmt.close();
                return null;
            }
            tOutData = rs.getBlob(1);
            rs.close();
            stmt.close();
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            try
            {
                if (rs != null)
                {
                    rs.close();
                }
                if (stmt != null)
                {
                    stmt.close();
                }
            }
            catch (Exception ex1)
            {
            }
            return null;
        }
        return tOutData;
    }
}
